# -*- coding: UTF-8 -*-
__author__ = "lishang"
__date__ = "$2021-5-18 16:36:43$"

import sqlite3
import re

#https://www.cnblogs.com/nori/p/9967716.html
class db():
    config={
        "file":"kmap.db"
    }
    #对象
    instance=''
    #链接
    conn=''
    #游标
    cursor=''
    
    def __init__(self):
        db.conn = sqlite3.connect(self.config['file'])
        #创建一个游标 curson
        db.cursor = db.conn.cursor()
        
    def __del__(self):
            #关闭游标：
        db.cursor.close()
        #提交事物
        db.conn.commit()
        #关闭连接
        db.conn.close()
            
    @staticmethod
    def execute(sql,params=()):
        print(sql)
        rt=db.cursor.execute(sql,params)
        if(re.compile(r'select',re.I).match(sql)):
            rt= db.cursor.fetchall()
            #fetchone, fetchmany(3) https://www.cnblogs.com/z-x-y/p/9055509.html
        # 通过rowcount获得插入的行数:
        elif(re.compile(r'insert|update',re.I).match(sql)):
            rt=db.cursor.rowcount
            db.conn.commit()
        return rt        
    

db.instance=db()

def execute(sql,params=()):
    return db.execute(sql,params)

def init():
    #sqlite字段说明： https://www.cnblogs.com/decwang/p/4565572.html
    sqlAll = {
        'path':[
            "CREATE TABLE `path` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `path` TEXT NOT NULL, `count` INTEGER DEFAULT 0, `uptime` INTEGER DEFAULT 0, `intime` INTEGER DEFAULT 0 )",
            "create unique index  path_index on path(path)"
        ],
        'pic':[
            "CREATE TABLE `pic` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `file` TEXT NOT NULL, `x` INTEGER DEFAULT 0,`y` INTEGER DEFAULT 0, `time` TEXT DEFAULT 0, `name` TEXT NOT NULL,`intime` INTEGER DEFAULT 0 ,`pid` INTEGER )",
            "create index  pic_index on pic(file,x,y,time,name,pid)"
        ],
    }
    # sql = "insert into login (name, password) values ('love', '520520');"
    # sql = "update t1 set b='name1.999999' where a = '887'"
    # sql = "create index  ia on t1(a,b)"
    for dbname in sqlAll: 
        # if dbname=='path': continue
        sqls=sqlAll[dbname]
        db.execute('drop table if  exists %s' % dbname)
        db.execute('drop index if  exists %s.%s_index' % (dbname,dbname))
        for  sql in sqls:
            db.execute(sql)

def insert(name,params):
    keys=""
    values=""
    for k in params :
            keys+="`%s`," % k
            values+="'%s'," % params[k]
    return db.execute("insert into `%s` (%s) values (%s)" % (name,keys[:-1],values[:-1]))        

def query(name,where,select='*',limit=1000):        
        return db.execute("select %s from %s where  %s limit  %s" % (select,name,where,limit))        

def queryObject(name,where,select='*',limit=1000):
        all=query(name,where,select,limit)
        title=[k.split(' as ').pop() for k in tuple(select.split(',')) ]
        return toDict(all,[] if select=='*' else title)

def queryId(name,where,select='id'):
    one=query(name,where,select,1)[0]
    return one[0]

def queryOne(name,where):
    one=query(name,where,"*",1)[0]
    row_headers=[x[0] for x in db.cursor.description]  
    return dict(zip(row_headers,one))

def delete(name,where):
     return db.execute("delete from `%s` where %s " % (name,where) )        
     
#https://blog.csdn.net/oh_futrue/article/details/108757014
def toDict(rows=[],row_headers=[]):
    if row_headers==[] :
        row_headers=[x[0] for x in db.cursor.description]  
    json_data=[]
    for result in rows:
        json_data.append(dict(zip(row_headers,result)))
    return json_data

